# coding=utf-8
"""
    Created by Kalyter on 2017-11-21.
"""

import csv

import datetime
import time

from sqlalchemy import func, text

from learning.app import db
import sys

reload(sys)
sys.setdefaultencoding('utf-8')


def read_source(filename):
    reader = csv.reader(open(filename))
    content = {}
    for line, row in enumerate(reader):
        if line > 0:
            user_id = row[0]
            open_id = row[1]
            create_time = datetime.datetime.strptime(row[2], '%Y-%m-%dT%H:%M:%S')
            create_time = time.mktime(create_time.timetuple())
            content.setdefault(user_id, [])
            content[user_id].append((create_time, open_id))
    print('读取源文件内容成功')
    return content


def main(filename):
    content = read_source(filename)
    ERROR_TIME = 30 * 60
    refresh_count = 0

    tasks = db.session\
        .query(db.Task.id, db.Task.app_user_id, db.Task.create_time)\
        .filter(db.Task.app_id == '1', text('LENGTH(app_user_id) < 20'))\
        .all()
    for task in tasks:
        if task.app_user_id in content:
            match_list = content[task.app_user_id]
            closest_index = 0
            task_time = time.mktime(task.create_time.timetuple())
            for i, match in enumerate(match_list):
                if i > 0:
                    closest_minus = match_list[closest_index][0] - task_time
                    match_minus = match_list[i][0] - task_time
                    if abs(match_minus) < abs(closest_minus):
                        # 时间相差取最小的
                        closest_index = i
            closest_time = match_list[closest_index][0]
            if abs(closest_time - task_time) < ERROR_TIME:
                open_id = match_list[closest_index][1]
                db.session.query(db.Task) \
                    .filter(db.Task.id == task.id) \
                    .update({db.Task.app_user_id: open_id})
                refresh_count += 1
                print('更新   %s  %s' % (task.id, open_id))
            else:
                print('时间太长，不更新 %s  task表%s     表格文件: %s'
                      % (task.id, task.create_time,
                         time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(closest_time))))
        else:
            print('跳过   %s  app_user_id: %s' % (task.id, task.app_user_id))
    print('一共更新%s个task' % refresh_count)


if __name__ == '__main__':
    main('./waimai_2017_11_21.csv')
